package com.china08.yxyapi.service.db.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.service.db.YxyAuthService;

@Service
public class YxyAuthServiceImpl implements YxyAuthService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public Map<String, Object> list(String schoolId, String catalogId1, String catalogId2, String keyword, Integer status, Integer pageNum) {

        List<Object> param = new ArrayList<Object>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append("     select                                             ");
        sb1.append("       count(t1.id)                                     ");
        sb1.append("      from yxy_lessons.yxy_lessons_school_lesson t1     ");
        sb1.append("      where t1.school_id = ?                            ");
        sb1.append("        and t1.is_visible = 1                           ");
        sb1.append("        and t1.status = 2                               ");
        param.add(schoolId);

        if (StringUtils.isNotBlank(catalogId1) && !"all".equals(catalogId1)) {
            sb1.append("     and t1.catalog_id1 = ?                     ");
            param.add(catalogId1);
        }
        if (StringUtils.isNotBlank(catalogId2) && !"all".equals(catalogId2)) {
            sb1.append("     and t1.catalog_id2 = ?                     ");
            param.add(catalogId2);
        }
        if (status == 1 || status == 0) {
            sb1.append("     and t1.vip = ?                             ");
            param.add(status);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append("     and t1.name like ?                         ");
            param.add(keyword);
        }
        Integer allSize = jdbcTemplate.queryForObject(sb1.toString(), param.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("      select                                            ");
        sb2.append("        t1.id,                                          ");
        sb2.append("        t1.name,                                        ");
        sb2.append("        t1.catalog_id1,                                 ");
        sb2.append("        t1.catalog_id2,                                 ");
        sb2.append("        t1.count,                                       ");
        sb2.append("        t1.vip                                          ");
        sb2.append("      from yxy_lessons.yxy_lessons_school_lesson t1     ");
        sb2.append("      where t1.school_id = ?                            ");
        sb2.append("        and t1.is_visible = 1                           ");
        sb2.append("        and t1.status = 2                               ");

        if (StringUtils.isNotBlank(catalogId1) && !"all".equals(catalogId1)) {
            sb2.append("     and t1.catalog_id1 = ?                         ");
        }
        if (StringUtils.isNotBlank(catalogId2) && !"all".equals(catalogId2)) {
            sb2.append("     and t1.catalog_id2 = ?                         ");
        }
        if (status == 1 || status == 0) {
            sb2.append("     and t1.vip = ?                                 ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append("     and t1.name like ?                             ");
        }
        sb2.append("         order by t1.last_modified_date  desc                 ");
        sb2.append("        limit ?,?                                       ");
        param.add((pageNum - 1) * 10);
        param.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), param.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allSize", allSize);
        map.put("list", list);
        return map;
    }

    @Override
    public Integer count(String schoolId, String lessonId) {
        StringBuffer sb1 = new StringBuffer();
        sb1.append("  select count(id)                                  ");
        sb1.append("    from yxy_lessons.yxy_lessons_school_lesson_auth ");
        sb1.append("  where school_id = ?                               ");
        sb1.append("    and lesson_id = ?                               ");
        return jdbcTemplate.queryForObject(sb1.toString(), new Object[] { schoolId, lessonId }, Integer.class);
    }

    @Override
    public Map<String, Object> authUserList(String lessonId, String classId, String keyword, Integer pageNum) {

        List<Object> paramList = new ArrayList<>();

        StringBuffer sBuffer = new StringBuffer();
        sBuffer.append("  select *                                                      ");
        sBuffer.append("  from yxy_lessons.yxy_lessons_school_lesson_auth               ");
        sBuffer.append("  where lesson_id = ?                                           ");
        sBuffer.append("  and status = 1                                                ");
        paramList.add(lessonId);

        if (StringUtils.isNotBlank(classId)) {
            sBuffer.append(" and class_id = ?                                           ");
            paramList.add(classId);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sBuffer.append(" and user_nick like ?                                       ");
            paramList.add(keyword);
        }
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sBuffer.toString(), paramList.toArray());
        Integer allSize = list.size();

        sBuffer.append(" order by last_modified_date desc                       ");
        sBuffer.append("     limit ?,?                                          ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        list = jdbcTemplate.queryForList(sBuffer.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<>();
        map.put("allSize", allSize);
        map.put("list", list);
        return map;
    }
}
